package com.yc.mybatis;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.yc.mybatis.bean.Hall;
import com.yc.mybatis.bean.Movie;
import com.yc.mybatis.bean.Plan;
import com.yc.mybatis.bean.User;
import com.yc.mybatis.mapper.*;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Date;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyBatisTester {

    @Test
    public void test() throws IOException {
        String resource = "mybatis.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 执行语句
        List<Map> list = sqlSession.selectList(
                "com.yc.mybatis.mapper.DemoMapper.selectUser");
        // 打印结果
        list.forEach(System.out::println);
        System.out.println("list.get(0).get(\"name\") = " + list.get(0).get("name"));

        ///////////////////
        DemoMapper mapper = sqlSession.getMapper(DemoMapper.class);
        List<User> users = mapper.selectUser1();
        users.forEach(System.out::println);
        System.out.println("users.get(0).getName() = " + users.get(0).getName());

        sqlSession.close();
    }


    SqlSession sqlSession;
    SqlSession sqlSession1;

    // 在測試方法执行前执行的方法
    @Before
    public void before() throws IOException {
        String resource = "mybatis.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建会话
        sqlSession = sqlSessionFactory.openSession();
        sqlSession1 = sqlSessionFactory.openSession();
    }

    // 在測試方法执行后执行的方法
    @After
    public void after() {
        sqlSession.close();
        sqlSession1.close();
    }

    @Test
    public void test1() {
        DemoMapper mapper = sqlSession.getMapper(DemoMapper.class);
        User user = mapper.selectById(1);
        Assert.assertEquals("张三", user.getName());

        // MyBatis默认自动回滚

        try {
            User newUser = new User();
            newUser.setId(2);
            newUser.setName("李四");
            newUser.setPwd("123");
            newUser.setBirthday(new Date(System.currentTimeMillis()));
            mapper.insert(newUser);
            user = mapper.selectById(2);
            Assert.assertEquals("李四", user.getName());
            // 如果正常执行完成, 则提交
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            // 如果执行出现异常, 则回滚
            sqlSession.rollback();
        } finally {
            // 不管正常还是异常都要关闭会话
            sqlSession.close();
        }

    }

    @Test
    public void test2() {
        DemoMapper mapper = sqlSession.getMapper(DemoMapper.class);
        try {
            User user = new User();
            user.setId(3);
            user.setName("王五");
            mapper.insert(user);
            User dbUser = mapper.selectById(3);
            Assert.assertEquals("王五", dbUser.getName());

            dbUser.setName("赵六");
            mapper.updateById(dbUser);
            dbUser = mapper.selectById(3);
            Assert.assertEquals("赵六", dbUser.getName());

            int i = mapper.deleteById(3);
            Assert.assertEquals(1, i);
            dbUser = mapper.selectById(3);
            Assert.assertNull(dbUser);

            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }

    @Test
    public void test3() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.selectForLogin("张三", "123");
    }

    @Test
    public void test4() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.selectForLogin1("张三");

        Map<String, Object> params = new HashMap<>();
        params.put("name", "李四");
        params.put("pwd", "123");
        mapper.selectForLogin2(params);

    }

    @Test
    public void test5() {
        MovieDao mapper = sqlSession.getMapper(MovieDao.class);
        List<Movie> list = mapper.selectLikeName("是");
        list.forEach(System.out::println);
    }

    @Test
    public void test6() {
        PlanMapper planMapper = sqlSession.getMapper(PlanMapper.class);
        MovieDao movieDao = sqlSession.getMapper(MovieDao.class);

        Plan plan = planMapper.selectById(1);
        Movie movie = movieDao.selectById(plan.getMid());

        System.out.println("plan = " + plan);
        System.out.println("movie = " + movie);

    }

    @Test
    public void test7() {
        PlanMapper planMapper = sqlSession.getMapper(PlanMapper.class);
        // 一对一关联查询
        Plan plan = planMapper.selectById1(1);
        // 默认以积极加载方式完成查询
        System.out.println("plan.getId() = " + plan.getId());
        System.out.println("plan.getPlayTime() = " + plan.getPlayTime());
        System.out.println("plan.getMid() = " + plan.getMid());
        // 使用 fetch="lazy" 开启延迟加载(惰性加载)
        System.out.println("plan.getMovie().getName() = " + plan.getMovie().getName());
        System.out.println("plan.getMovie().getLength() = " + plan.getMovie().getLength());
        System.out.println("plan.getMovie().getType() = " + plan.getMovie().getType());
        //Movie movie = plan.getMovie();
        //System.out.println("plan = " + plan);
        //System.out.println("movie = " + movie);
    }

    @Test
    public void test8() {
        PlanMapper planMapper = sqlSession.getMapper(PlanMapper.class);
        // 一对一关联查询
        Plan plan = planMapper.selectById2(1);
        System.out.println("plan.getId() = " + plan.getId());
        System.out.println("plan.getPlayTime() = " + plan.getPlayTime());
        System.out.println("plan.getMid() = " + plan.getMid());
        System.out.println("plan.getMovie().getName() = " + plan.getMovie().getName());
        System.out.println("plan.getMovie().getLength() = " + plan.getMovie().getLength());
        System.out.println("plan.getMovie().getType() = " + plan.getMovie().getType());
    }

    @Test
    public void test9() {
        PlanMapper planMapper = sqlSession.getMapper(PlanMapper.class);
        // 一对一关联查询
        Plan plan = planMapper.selectById3(1);
        System.out.println("plan.getId() = " + plan.getId());
        System.out.println("plan.getPlayTime() = " + plan.getPlayTime());
        System.out.println("plan.getMid() = " + plan.getMid());
        System.out.println("plan.getMovie().getName() = " + plan.getMovie().getName());
        System.out.println("plan.getMovie().getLength() = " + plan.getMovie().getLength());
        System.out.println("plan.getMovie().getType() = " + plan.getMovie().getType());
    }

    // 一对多关联查询
    @Test
    public void test10() {
        MovieDao movieDao = sqlSession.getMapper(MovieDao.class);
        Movie movie = movieDao.selectById(1);
        List<Plan> plans = movie.getPlans();
        plans.forEach(System.out::println);
    }

    @Test
    public void test11() {
        MovieDao movieDao = sqlSession.getMapper(MovieDao.class);
        Movie movie = null;
        movieDao.selectByObject(movie);
        System.out.println("=============================");
        movie = new Movie();
        movieDao.selectByObject(movie);
        System.out.println("=============================");
        movie.setIntro(".....");
        movieDao.selectByObject(movie);
        System.out.println("=============================");
        movie.setIs3d(true);
        movieDao.selectByObject(movie);
        System.out.println("=============================");
        movie.setType("xxxxx");
        movieDao.selectByObject(movie);
        System.out.println("=============================");
        movie.setName("?????");
        movieDao.selectByObject(movie);
        System.out.println("=============================");
        movie.setIntro(null);
        movieDao.selectByObject(movie);
        System.out.println("=============================");
    }

    @Test
    public void test12() {
        MovieDao movieDao = sqlSession.getMapper(MovieDao.class);
        Movie movie = new Movie();
        movie.setId(1);
        movie.setLength(200);
        Movie dbMovie = movieDao.selectById(1);
        System.out.println("更新前 dbMovie = " + dbMovie);
        movieDao.updateById(movie);
        dbMovie = movieDao.selectById(1);
        System.out.println("更新后 dbMovie = " + dbMovie);
        sqlSession.rollback();

        System.out.println("-----------------------------------------------");
        dbMovie = movieDao.selectById(1);
        System.out.println("更新前 dbMovie = " + dbMovie);
        movieDao.updateByIdWithoutNull(movie);
        dbMovie = movieDao.selectById(1);
        System.out.println("更新后 dbMovie = " + dbMovie);
        sqlSession.rollback();

        System.out.println("------------------ 更新指定的字段 instro 为 null 值 -----------------------------");
        dbMovie = movieDao.selectById(1);
        System.out.println("更新前 dbMovie = " + dbMovie);
        // 使用数据库查询出的对象进行更新
        dbMovie.setIntro(null);
        movieDao.updateById(dbMovie);
        dbMovie = movieDao.selectById(1);
        System.out.println("更新后 dbMovie = " + dbMovie);
        sqlSession.rollback();

    }

    @Test
    public void test13() {
        MovieDao movieDao = sqlSession.getMapper(MovieDao.class);
        movieDao.selectByNameAndFlag("xxxx", 1);
        movieDao.selectByNameAndFlag("xxxx", 2);
        movieDao.selectByNameAndFlag("xxxx", 3);
        movieDao.selectByNameAndFlag("xxxx", 4);
        movieDao.selectByNameAndFlag("xxxx", 5);
    }

    @Test
    public void test14() {
        MovieDao movieDao = sqlSession.getMapper(MovieDao.class);
        int[] ids = {1,2,3};
        List<Movie> list = movieDao.selectByIdArray(ids);
        Assert.assertEquals(3,list.size());
        ids = new int[]{1,5,3,4};
        list = movieDao.selectByIdArray(ids);
        Assert.assertEquals(4,list.size());
    }

    @Test
    public void test15() {
        MovieDao movieDao = sqlSession.getMapper(MovieDao.class);
        List<Movie> movies = new ArrayList<>();
        Movie m1 = new Movie();
        m1.setName("a");
        m1.setLength(100);
        Movie m2 = new Movie();
        m2.setName("b");
        m2.setLength(200);
        movies.add(m1);
        movies.add(m2);
        movieDao.insertMovies(movies);
    }

    @Test
    public void test16() {
        MovieDao movieDao = sqlSession.getMapper(MovieDao.class);
        movieDao.selectWithRole("MGR");
        movieDao.selectWithRole("SALE");

        System.out.println("=================================");

        movieDao.selectTime().forEach(System.out::println);

        System.out.println("=================================");

        movieDao.selectOrderBy(" length desc");
        movieDao.selectOrderBy(" begin_time desc, id, name desc");

        System.out.println("=================================");

        movieDao.selectBySQL("select now()").forEach(System.out::println);

        movieDao.selectBySQL("select * from sp_hall").forEach(System.out::println);

        movieDao.selectBySQL("select * from sp_movie where name like '%我%'").forEach(System.out::println);
    }

    @Test
    public void test17() {
        HallMapper mapper = sqlSession.getMapper(HallMapper.class);
        Hall hall = new Hall();
        hall.setName("测试影厅");
        hall.setIs3d(true);
        hall.setSeats(50);
        hall.setSize("小");
        System.out.println("hall = " + hall);
        mapper.insert(hall);
        System.out.println("hall = " + hall);
        // select max(id) from sp_hall;
    }

    @Test
    public void test18() {
        HallMapper mapper = sqlSession.getMapper(HallMapper.class);
        System.out.println("========================");
        Hall hall1 = mapper.selectById(1);
        test17();
        System.out.println("========================");
        Hall hall2 = mapper.selectById(1);
        System.out.println("========================");
    }

    @Test
    public void test19() {
        HallMapper mapper = sqlSession.getMapper(HallMapper.class);
        HallMapper mapper1 = sqlSession1.getMapper(HallMapper.class);
        System.out.println("========================");
        Hall hall = mapper.selectById(1);
        // 提交才会共享数据
        sqlSession.commit();
        System.out.println("========================");
        Hall hall1 = mapper1.selectById(1);
        System.out.println("========================");
        mapper1.selectById(1);
        System.out.println("========================");
        mapper1.selectById(1);
        System.out.println("========================");
        // Cache Hit Ratio 缓存命中率

        System.out.println("hall.equals(hall1) => " + hall.equals(hall1));
        System.out.println("hall == hall1 => " + (hall == hall1));

    }

    @Test
    public void test20() {
        HallMapper mapper = sqlSession.getMapper(HallMapper.class);
        Hall hall = new Hall();
        hall.setName("测试影厅");
        hall.setIs3d(true);
        hall.setSeats(50);
        hall.setSize("小");
        System.out.println("hall = " + hall);
        mapper.insert1(hall);
        System.out.println("hall = " + hall);
        System.out.println("mapper.selectById1(hall.getId()) = " + mapper.selectById1(hall.getId()));
        System.out.println("---------------------------------");
        Movie movie = new Movie();
        movie.setName("xxxx");
        movie.setIntro("yyyy");
        mapper.selectMovieByObject(movie);
        System.out.println("---------------------------------------");
        Plan plan = mapper.selectPlanById(1);
        System.out.println("plan.getPlayTime() = " + plan.getPlayTime());
        System.out.println("plan.getId() = " + plan.getId());
        System.out.println("plan.getMovie().getName() = " + plan.getMovie().getName());
    }


    @Test
    public void test21() {
        MovieDao mapper = sqlSession.getMapper(MovieDao.class);
        System.out.println("===========================");
        List<Movie> movies = mapper.selectLikeName("");
        movies.forEach(System.out::println);
        System.out.println("===========================");
        PageHelper.startPage(1, 2);
        // 执行查询之前, 设置分页参数
        movies = mapper.selectLikeName("");
        movies.forEach(System.out::println);
        System.out.println("===========================");
        Page<Object> page = PageHelper.startPage(2, 3, true);
        movies = mapper.selectLikeName("");
        System.out.println("page.getTotal() = " + page.getTotal());
        System.out.println("(page.hashCode() == movies.hashCode()) = " + (page.hashCode() == movies.hashCode()));
        movies.forEach(System.out::println);
    }
}

